﻿
Partial Class WebForm_ManageSell_OutStockExcelUpload
    Inherits System.Web.UI.Page
#Region "事件"
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Me.BindType()
            Me.divMatch.Visible = False
            Me.BindFileColumnName()
            Me.BindDDLCustomerGroup()
        End If
        Me.Form.Enctype = "multipart/form-data"
    End Sub
    Protected Sub ddlType_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlType.SelectedIndexChanged
        If ddlType.SelectedValue = "SellOut" Then
            lblYMonth.Visible = True
            ddlYMonth.Visible = True
            Me.BindYearMonth()
        Else
            lblYMonth.Visible = False
            ddlYMonth.Visible = False
        End If
    End Sub
    Protected Sub gvMatch_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvMatch.RowDataBound
        If e.Row.RowType = DataControlRowType.DataRow Then
            Dim ddlFileColumnName As DropDownList = TryCast(e.Row.FindControl("ddlFileColumnName"), DropDownList)
            ddlFileColumnName.DataTextField = "FileColumnName"
            ddlFileColumnName.DataValueField = "FileColumnName"
            ddlFileColumnName.DataSource = Me.FileColumnName
            ddlFileColumnName.DataBind()
            ddlFileColumnName.Items.Insert(0, MyObj.GetDDLPleaseChooseItem())
        End If
    End Sub

    Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click
        If Not FileUpload1.HasFile Then
            MyObj.ShowMessage("no file")
            Return
        ElseIf FileUpload1.PostedFile.ContentType.ToString() <> "application/vnd.ms-excel" AndAlso FileUpload1.PostedFile.ContentType.ToString() <> "application/octet-stream" Then
            MyObj.ShowMessage("Import failed!!Please select the file extension (xls, csv) file!!")
            Return
        End If

        '上傳暫存檔
        Dim clsRandom As New clsRandom()
        Dim newName As String = Server.MapPath("~/") & "/tmp/" & clsRandom.Generate()
        FileUpload1.PostedFile.SaveAs(newName)

        Dim ExcelImportCheck As New ExcelImportCheck()
        Dim dt As New Data.DataTable

        Me.BindDBColumnName()
        '檢查工作表名稱

        If ExcelImportCheck.LoadData(dt, newName, ddlType.SelectedValue, IO.Path.GetExtension(FileUpload1.PostedFile.FileName).ToUpper()) = False Then
            Return
        Else
            Me.ImportData = dt
        End If



        If Me.ImportData.Rows.Count > 0 Then
            Me.FileColumnName.Rows.Clear()
            For i As Integer = Me.ImportData.Columns.Count - 1 To 0 Step -1
                '去除空的Column
                If Me.ImportData.Columns(i).ColumnName.Substring(0, 1) <> "F" Then
                    Dim row As Data.DataRow
                    row = Me.FileColumnName.NewRow()
                    row("FileColumnName") = Me.ImportData.Columns(i).ColumnName
                    Me.FileColumnName.Rows.InsertAt(row, 0)
                Else
                    Me.ImportData.Columns.Remove(Me.ImportData.Columns(i).ColumnName)
                End If

            Next


            Me.divMatch.Visible = True
            Me.gvMatch.DataSource = Me.DBColumnName
            Me.gvMatch.DataBind()

            '幫它預選欄位
            Dim ColumnString As String = ""
            Dim ColumnFstSelect As String = "SAP,貨號,品號"
            Dim ColumnSecSelect As String = "WSN,銷售總量,數量,庫存量,銷售數"
            Dim FstColumnValue As String = ""
            Dim SecColumnValue As String = ""
            For b As Integer = 0 To Me.ImportData.Columns.Count - 1
                ColumnString &= Me.ImportData.Columns(b).ColumnName & ","
            Next

            '去掉最後一個逗號
            ColumnString = ColumnString.Substring(0, ColumnString.Length - 1)

            For Each ColString As String In ColumnString.Split(",")
                For Each SelecFstString As String In ColumnFstSelect.Split(",")
                    If ColString = SelecFstString Then
                        FstColumnValue = SelecFstString
                    End If
                Next

                For Each SelectSecString As String In ColumnSecSelect.Split(",")
                    If ColString = SelectSecString Then
                        SecColumnValue = SelectSecString
                    End If
                Next
            Next


            TryCast(Me.gvMatch.Rows(0).FindControl("ddlFileColumnName"), DropDownList).SelectedValue = FstColumnValue
            TryCast(Me.gvMatch.Rows(1).FindControl("ddlFileColumnName"), DropDownList).SelectedValue = SecColumnValue

        Else
            MyObj.ShowMessage("No Data is available for Import!!")
        End If



    End Sub

    Protected Sub btnSent_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSent.Click
        For i As Integer = 0 To Me.gvMatch.Rows.Count - 1
            Dim ddlFileColumnName As DropDownList = TryCast(Me.gvMatch.Rows(i).FindControl("ddlFileColumnName"), DropDownList)
            If ddlFileColumnName.SelectedValue = "" Then
                MyObj.ShowMessage("Please confirm that your file column name exactly match your system column name!")
                Return
            End If
        Next


        '組去空值的字串
        Dim FilterValue As String = ""
        For i As Integer = 0 To Me.ImportData.Columns.Count - 1
            If FilterValue = "" Then
                FilterValue &= "convert([" & Me.ImportData.Columns(0).ColumnName.ToString() & "],System.String)<>''"
            Else
                FilterValue &= " or convert([" & Me.ImportData.Columns(i).ColumnName.ToString() & "],System.String)<>''"
            End If
        Next

        '新增Error欄位
        Me.ImportData.Columns.Add("Error", System.Type.GetType("System.String"))

        '開始去空值
        Dim MyDefaultView As Data.DataView = Me.ImportData.DefaultView
        MyDefaultView.RowFilter = FilterValue
        Me.ImportData = MyDefaultView.ToTable()

        Dim sql As New MyStringBuilder
        Dim dat As New clsData
        Dim MappingTable As Data.DataTable
        Dim SapTable As Data.DataTable
        Dim AuthorityTable As Data.DataTable

        '準備SapCode資料
        sql.AppendFormat("select distinct SapCode from ProductData ")
        SapTable = dat.GetData(sql)
        SapTable.PrimaryKey = New DataColumn() {SapTable.Columns(0)}

        '準備WSN Code
        sql.Length = 0
        sql.AppendFormat("Select distinct MarketCode from SapCodeMarketCodeMapping ")
        MappingTable = dat.GetData(sql)
        MappingTable.PrimaryKey = New DataColumn() {MappingTable.Columns(0)}

        '準備Signature權限
        sql.Length = 0
        sql.AppendFormat("select distinct s.MarketCode from SapCodeMarketCodeMapping s ")
        sql.AppendFormat("inner join ProductData  p  ")
        sql.AppendFormat("on s.SapCode=p.SapCode ")
        sql.AppendFormat("left join ChannelView  c on  left(p.ProdHierarchy,2)=c.Signature ")
        sql.AppendFormat("and s.ChannelID=c.Channel ")
        AuthorityTable = dat.GetData(sql)
        AuthorityTable.PrimaryKey = New DataColumn() {AuthorityTable.Columns(0)}

        Dim ErrorCount As Integer = 0
        Dim colFstName, colSecName As String
        colFstName = TryCast(Me.gvMatch.Rows(0).FindControl("ddlFileColumnName"), DropDownList).SelectedValue
        colSecName = TryCast(Me.gvMatch.Rows(1).FindControl("ddlFileColumnName"), DropDownList).SelectedValue


        '開始檢查資料
        For i As Integer = 0 To Me.ImportData.Rows.Count - 1

            If ddlType.SelectedValue = "SCMP" Then
                '比對SapCode是否存在
                If Not SapTable.Rows.Contains(ImportData.Rows(i)(colFstName).ToString()) Then
                    Me.ImportData.Rows(i)("Error") = "The Sap Code dosen't exist."
                    ErrorCount += 1
                    Continue For
                End If

                '比對WSN Code格式
                If Me.ImportData.Rows(i)(colSecName).ToString().Length <> 6 Then
                    Me.ImportData.Rows(i)("Error") = "The format of Market Code is'nt same."
                    ErrorCount += 1
                    Continue For
                End If
            Else

                'Signature權限
                If ddlType.SelectedValue = "SellOut" And Not AuthorityTable.Rows.Contains(ImportData.Rows(i)(colFstName).ToString()) Then
                    Me.ImportData.Rows(i)("Error") = "The Sap Code is out of your scope."
                    ErrorCount += 1
                    Continue For
                End If

                '貨號是否存在
                If Not MappingTable.Rows.Contains(ImportData.Rows(i)(colFstName).ToString()) Then
                    Me.ImportData.Rows(i)("Error") = "The Market Code dosen't exist."
                    ErrorCount += 1
                    Continue For
                End If

            End If


            '沒有錯誤則SHOW成功訊息
            'If Me.ImportData.Rows(i)("Error").ToString() = "" Then
            '    Me.ImportData.Rows(i)("Error") = "Upload Successful"
            'End If
        Next

        '有異常則不寫入資料,反之則寫入
        If (ErrorCount > 0) Then
            MyObj.ShowMessage("Upload Failed.Data Error Count:" & ErrorCount)
        Else
            If ImportData.Rows.Count > 0 Then

                For a As Integer = 0 To ImportData.Rows.Count - 1
                    sql.Length = 0
                    Select Case ddlType.SelectedValue
                        Case "SCMP"
                            Dim SCMPView As DataView = SapTable.DefaultView
                            SCMPView.RowFilter = String.Format("[SapCode] = '{0}' ", Me.ImportData.Rows(a)(colFstName))

                            If SCMPView.Count < 1 Then
                                sql.AppendFormat("Insert Into SapCodeMarketCodeMapping(SapCode,MarketCode,ChannelID) ")
                                sql.AppendFormat("select '{0}' ", ImportData.Rows(a)(colFstName))
                                sql.AppendFormat(", '{0}' ", ImportData.Rows(a)(colSecName))
                                sql.AppendFormat(", '{0}' ", ddlCustomerGroup.SelectedValue)
                                sql.AppendFormat(";")
                            Else
                                sql.AppendFormat("update SapCodeMarketCodeMapping Set ")
                                sql.AppendFormat("MarketCode='{0}' ", ImportData.Rows(a)(colSecName))
                                sql.AppendFormat("where SapCode='{0}' ", ImportData.Rows(a)(colFstName))
                                sql.AppendFormat("and ChannelID='{0}' ", ddlCustomerGroup.SelectedValue)
                                sql.AppendFormat(";")
                            End If
                        Case "SellOut"
                            Dim TableName As String = "SFReport" & ddlYMonth.SelectedValue.Substring(0, 4) & "A"
                            Dim Month As String = ""

                            If ddlYMonth.SelectedValue.Substring(5, 1).ToString() <> "0" Then
                                Month = ddlYMonth.SelectedValue.Substring(5, 2).ToString()
                            Else
                                Month = ddlYMonth.SelectedValue.Substring(6, 1).ToString()
                            End If

                            Dim dt As Data.DataTable = MarketCodeToSapCode(Me.ImportData.Rows(a)(colFstName))

                            sql.Length = 0
                            sql.AppendFormat("Insert Into {0}(SapCode,SkuType,ChannelID,Mon{1}) ", TableName, Month)
                            sql.AppendFormat("Select '{0}' ", dt.Rows(0)("SapCode").ToString())
                            sql.AppendFormat(",'{0}' ", ddlType.SelectedValue)
                            sql.AppendFormat(",'{0}' ", ddlCustomerGroup.SelectedValue)
                            sql.AppendFormat(",'{0}' ", Me.ImportData.Rows(a)(colSecName))
                            sql.AppendFormat("where not exists ( ")
                            sql.AppendFormat("select * from {0} ", TableName)
                            sql.AppendFormat("where SapCode='{0}' ", dt.Rows(0)("SapCode").ToString())
                            sql.AppendFormat("and SkuType='{0}' ", ddlType.SelectedValue)
                            sql.AppendFormat("and ChannelID='{0}' ) ", ddlCustomerGroup.SelectedValue)
                            sql.AppendFormat(";")

                            sql.AppendFormat("Update {0} SET ", TableName)
                            sql.AppendFormat("Mon{0}='{1}' ", Month, Me.ImportData.Rows(a)(colSecName))
                            sql.AppendFormat("where SapCode = '{0} '", dt.Rows(0)("SapCode").ToString())
                            sql.AppendFormat("and SkuType = '{0}' ", ddlType.SelectedValue)
                            sql.AppendFormat("and ChannelID = '{0}' ", ddlCustomerGroup.SelectedValue)
                            sql.AppendFormat(";")

                        Case "TradStock"


                            Dim sdt As Data.DataTable = MarketCodeToSapCode(Me.ImportData.Rows(a)(colFstName))

                            sql.Length = 0
                            sql.AppendFormat("Insert Into ProductTradeStock(SapCode,CustomerGroup,CustomerGroupName,Stock) ")
                            sql.AppendFormat("Select '{0}' ", sdt.Rows(0)("SapCode").ToString())
                            sql.AppendFormat(",'{0}' ", ddlCustomerGroup.SelectedValue)
                            sql.AppendFormat(",'{0}' ", ddlCustomerGroup.SelectedItem.Text)
                            sql.AppendFormat(",'{0}' ", Me.ImportData.Rows(a)(colSecName))
                            sql.AppendFormat("where not exists( ")
                            sql.AppendFormat("select * from ProductTradeStock ")
                            sql.AppendFormat("where SapCode='{0}' ", sdt.Rows(0)("SapCode").ToString())
                            sql.AppendFormat("and CustomerGroup='{0}' ", ddlCustomerGroup.SelectedValue)
                            sql.AppendFormat(";")

                            sql.AppendFormat("Update ProductTradeStock SET ")
                            sql.AppendFormat("Stock='{0}' ", sdt.Rows(0)("SapCode").ToString())
                            sql.AppendFormat("where SapCode ='{0}' ", sdt.Rows(0)("SapCode").ToString())
                            sql.AppendFormat("and CustomerGroup='{0}' ", ddlCustomerGroup.SelectedValue)
                            sql.AppendFormat(";")
                    End Select

                    dat.AccData(sql)

                Next
                MyObj.ShowMessage("Upload Successful.Data Success Count:" & ImportData.Rows.Count)
            Else
                MyObj.ShowMessage("Upload Faild.")
            End If
        End If

        '把Error移到第一個
        For k As Integer = 0 To Me.ImportData.Columns.Count - 1
            If k = Me.ImportData.Columns.Count - 1 Then
                Me.ImportData.Columns(k).SetOrdinal(0)
            End If
        Next

        ImportData.DefaultView.RowFilter = "convert(Error,System.String)<>''"
        ImportData = ImportData.DefaultView.ToTable

        Me.BindGV()

    End Sub
    Protected Sub gv_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles gv.PageIndexChanging
        Me.gv.PageIndex = e.NewPageIndex
        Me.BindGV()
    End Sub
#End Region

#Region "方法"
    Protected Function MarketCodeToSapCode(ByVal Market As String) As Data.DataTable
        Dim sql As New MyStringBuilder
        Dim dat As New clsData

        sql.Length = 0
        sql.AppendFormat("Select * from SapCodeMarketCodeMapping ")
        sql.AppendFormat("where MarketCode='{0}' ", Market)

        Return dat.GetData(sql)
    End Function
#End Region

#Region "繫結資料"
    Protected Sub BindDDLCustomerGroup()
        ddlCustomerGroup.DataTextField = "ChannelName"
        ddlCustomerGroup.DataValueField = "Channel"

        Dim sql As New MyStringBuilder
        Dim dat As New clsData
        sql.Length = 0
        sql.AppendFormat("SELECT distinct Channel,ChannelName FROM ChannelView c ")
        sql.AppendFormat("left join Role r on c.Division=r.Division ")
        sql.AppendFormat("left join RoleUserMapping ru on r.RoleID =ru.RoleID ")
        sql.AppendFormat("where ru.UserID='{0}' ", clsAuth.GetUserID())

        ddlCustomerGroup.DataSource = dat.GetData(sql)
        ddlCustomerGroup.DataBind()
        ddlCustomerGroup.Items.Insert(0, MyObj.GetDDLPleaseChooseItem())
    End Sub
    Protected Sub BindGV()
        gv.DataSource = ImportData
        gv.DataBind()
    End Sub
    Protected Sub BindType()
        ddlType.DataTextField = "Text"
        ddlType.DataValueField = "Value"

        ddlType.DataSource = MyObj.GetCodeMappingData("Product", "Import")
        ddlType.DataBind()
        ddlType.Items.Insert(0, MyObj.GetDDLPleaseChooseItem())
    End Sub
    Protected Sub BindYearMonth()
        Dim MonthCount As Integer = -(12 * (DateTime.Now.Year - 2008) + (DateTime.Now.Month - 1))
        For i As Integer = MonthCount To 0
            ddlYMonth.Items.Add(DateTime.Today.AddMonths(i).ToString("yyyy/MM"))

        Next
    End Sub

    Protected Sub BindFileColumnName()
        Dim MyDt As New Data.DataTable
        Dim dc As New Data.DataColumn("FileColumnName", System.Type.[GetType]("System.String"))
        MyDt.Columns.Add(dc)

        Me.FileColumnName = MyDt
    End Sub
    Protected Sub BindDBColumnName()
        Dim MyDt As New Data.DataTable
        Dim dc As New Data.DataColumn("ColumnName", System.Type.[GetType]("System.String"))
        MyDt.Columns.Add(dc)

        Dim MyColumnFirst As String = ""
        Dim MyColumnSecond As String = ""
        Select Case ddlType.SelectedValue
            Case "SCMP"
                MyColumnFirst = "SapCode"
                MyColumnSecond = "MarketCode"
            Case "SellOut"
                MyColumnFirst = "MarketCode"
                MyColumnSecond = "sell-out"
            Case "TradeStock"
                MyColumnFirst = "MarketCode"
                MyColumnSecond = "trade stock"
        End Select



        Dim row As Data.DataRow

        row = MyDt.NewRow()
        row("ColumnName") = MyColumnFirst
        MyDt.Rows.Add(row)

        row = MyDt.NewRow()
        row("ColumnName") = MyColumnSecond
        MyDt.Rows.Add(row)


        Me.DBColumnName = MyDt
    End Sub
#End Region

#Region "屬性"
    Property ImportData() As Data.DataTable
        Get
            Return ViewState("ImportData")
        End Get
        Set(ByVal value As Data.DataTable)
            ViewState("ImportData") = value
        End Set
    End Property
    Property DBColumnName() As Data.DataTable
        Get
            Return ViewState("DBColumnName")
        End Get
        Set(ByVal value As Data.DataTable)
            ViewState("DBColumnName") = value
        End Set
    End Property
    Property FileColumnName() As Data.DataTable
        Get
            Return ViewState("FileColumnName")
        End Get
        Set(ByVal value As Data.DataTable)
            ViewState("FileColumnName") = value
        End Set
    End Property
#End Region
End Class
